﻿// My methods here.
//-----------------------------------------------------------------------
// <Copyright file=YouTube company=DHPD>
// Copyright (c) DHPD Corporation (http://dhpd.edu.vn). All rights reserved.
// </copyright>
// <author>HP</author>
//-----------------------------------------------------------------------


using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using TV.DAT;
using TV.ENT;
using TV.Models;

namespace TV.DAT
{
    /// <summary>
    /// This is datYouTube class
    /// </summary>

    public class datYouTube
    {
        public datYouTube()
        {
            //
            // TODO: Add constructor logic here
            //
        }
        SqlCommand cmd;
        SqlDataReader dr;
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        #region  === tạo mới và sửa YouTube===
        public void createEdit(entYouTube entYT)
        {
            SqlConnection sqlConnection = new SqlConnection(constr);
            sqlConnection.Open();
            cmd = new SqlCommand("sp_YouTubeCreateEdit", sqlConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("YouTubeId", entYT.YouTubeId);
            cmd.Parameters.AddWithValue("Name", entYT.Name);
            cmd.Parameters.AddWithValue("Images", entYT.Images);
            cmd.Parameters.AddWithValue("Icon", entYT.Icon);
            cmd.Parameters.AddWithValue("Link", entYT.Link);
            cmd.Parameters.AddWithValue("ThuTu", entYT.ThuTu);
            cmd.Parameters.AddWithValue("TrangThai", entYT.TrangThai);
            cmd.Parameters.AddWithValue("NgayTao", entYT.NgayTao);

            cmd.ExecuteNonQuery();
            sqlConnection.Close();
        }
        #endregion

        #region  === getall YouTube===
        public List<entYouTube> getAll(ref PagingInfoModel page)
        {
            SqlConnection sqlConnection = new SqlConnection(constr);
            sqlConnection.Open();
            cmd = new SqlCommand("sp_YouTubeGetAll", sqlConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@PageIndex", page.CurrentPage);
            cmd.Parameters.AddWithValue("@PageSize", page.recordPerPage);
            cmd.Parameters.AddWithValue("@RecordCount", page.totalRecords).Direction = ParameterDirection.Output;
            dr = cmd.ExecuteReader();
            entYouTube entYT = new entYouTube();
            List<entYouTube> lst = new List<entYouTube>();
            while (dr.Read())
            {
                entYT = new entYouTube();
                entYT.YouTubeId = Convert.ToInt32(dr["YouTubeId"].ToString());
                entYT.Name = dr["Name"].ToString();
                entYT.Images = dr["Images"].ToString();
                entYT.Icon = dr["Icon"].ToString();
                entYT.Link = dr["Link"].ToString();
                entYT.ThuTu = Convert.ToInt32(dr["ThuTu"].ToString());
                entYT.TrangThai = Convert.ToByte(dr["TrangThai"].ToString());
                entYT.NgayTao = dr["NgayTao"].ToString();

                lst.Add(entYT);
            }

            dr.Close();
            if (lst.Count > 0)
            {
                // int.Parse(cmd.Parameters["@RecordCount"].Value.ToString());
                page.totalRecords = int.Parse(cmd.Parameters["@RecordCount"].Value.ToString());
            }

            sqlConnection.Close();
            return lst;
        }
        #endregion
        #region  === getall YouTube===
        public List<entYouTube> getAll(int trangthai, ref PagingInfoModel page)
        {
            SqlConnection sqlConnection = new SqlConnection(constr);
            sqlConnection.Open();
            cmd = new SqlCommand("sp_YouTubeGetAllByTrangThai2", sqlConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("trangthai", trangthai);
            cmd.Parameters.AddWithValue("@PageIndex", page.CurrentPage);
            cmd.Parameters.AddWithValue("@PageSize", page.recordPerPage);
            cmd.Parameters.AddWithValue("@RecordCount", page.totalRecords).Direction = ParameterDirection.Output;
            dr = cmd.ExecuteReader();
            entYouTube entYT = new entYouTube();
            List<entYouTube> lst = new List<entYouTube>();
            while (dr.Read())
            {
                entYT = new entYouTube();
                entYT.YouTubeId = Convert.ToInt32(dr["YouTubeId"].ToString());
                entYT.Name = dr["Name"].ToString();
                entYT.Images = dr["Images"].ToString();
                entYT.Icon = dr["Icon"].ToString();
                entYT.Link = dr["Link"].ToString();
                entYT.ThuTu = Convert.ToInt32(dr["ThuTu"].ToString());
                entYT.TrangThai = Convert.ToByte(dr["TrangThai"].ToString());
                entYT.NgayTao = dr["NgayTao"].ToString();

                lst.Add(entYT);
            }

            dr.Close();
            if (lst.Count > 0)
            {
                // int.Parse(cmd.Parameters["@RecordCount"].Value.ToString());
                page.totalRecords = int.Parse(cmd.Parameters["@RecordCount"].Value.ToString());
            }

            sqlConnection.Close();
            return lst;
        }
        #endregion
        #region  === getall YouTube by trang thai===
        public List<entYouTube> getAll(int trangthai)
        {
            SqlConnection sqlConnection = new SqlConnection(constr);
            sqlConnection.Open();
            cmd = new SqlCommand("sp_YouTubeGetAllByTrangThai", sqlConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@TrangThai", trangthai);
            
            dr = cmd.ExecuteReader();
            entYouTube entYT = new entYouTube();
            List<entYouTube> lst = new List<entYouTube>();
            while (dr.Read())
            {
                entYT = new entYouTube();
                entYT.YouTubeId = Convert.ToInt32(dr["YouTubeId"].ToString());
                entYT.Name = dr["Name"].ToString();
                entYT.Images = dr["Images"].ToString();
                entYT.Icon = dr["Icon"].ToString();
                entYT.Link = dr["Link"].ToString();
                entYT.ThuTu = Convert.ToInt32(dr["ThuTu"].ToString());
                entYT.TrangThai = Convert.ToByte(dr["TrangThai"].ToString());
                entYT.NgayTao = dr["NgayTao"].ToString();

                lst.Add(entYT);
            }

            dr.Close();
            sqlConnection.Close();
            return lst;
        }
        #endregion
        #region  === getall YouTube===
        public List<entYouTube> getAllByForeignKey(int Id, ref PagingInfoModel page)
        {
            SqlConnection sqlConnection = new SqlConnection(constr);
            sqlConnection.Open();
            cmd = new SqlCommand("sp_YouTubeGetAllByForeignKey", sqlConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("Id", Id);
            cmd.Parameters.AddWithValue("@PageIndex", page.CurrentPage);
            cmd.Parameters.AddWithValue("@PageSize", page.recordPerPage);
            cmd.Parameters.AddWithValue("@RecordCount", page.totalRecords).Direction = ParameterDirection.Output;
            dr = cmd.ExecuteReader();
            entYouTube entYT = new entYouTube();
            List<entYouTube> lst = new List<entYouTube>();
            while (dr.Read())
            {
                entYT = new entYouTube();
                entYT.YouTubeId = Convert.ToInt32(dr["YouTubeId"].ToString());
                entYT.Name = dr["Name"].ToString();
                entYT.Images = dr["Images"].ToString();
                entYT.Icon = dr["Icon"].ToString();
                entYT.Link = dr["Link"].ToString();
                entYT.ThuTu = Convert.ToInt32(dr["ThuTu"].ToString());
                entYT.TrangThai = Convert.ToByte(dr["TrangThai"].ToString());
                entYT.NgayTao = dr["NgayTao"].ToString();

                lst.Add(entYT);
            }

            dr.Close();
            if (lst.Count > 0)
            {
                // int.Parse(cmd.Parameters["@RecordCount"].Value.ToString());
                page.totalRecords = int.Parse(cmd.Parameters["@RecordCount"].Value.ToString());
            }

            sqlConnection.Close();
            return lst;
        }
        #endregion

        #region  === getinfo YouTube===
        public entYouTube getInfo(int YouTubeId)
        {
            SqlConnection sqlConnection = new SqlConnection(constr);
            sqlConnection.Open();
            cmd = new SqlCommand("sp_YouTubeGetInfo", sqlConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter[] sqla = new SqlParameter[1];
            sqla[0] = new SqlParameter("YouTubeId", DbType.Int32);
            sqla[0].Value = YouTubeId;

            cmd.Parameters.AddRange(sqla);
            dr = cmd.ExecuteReader();
            entYouTube entYT = new entYouTube();
            if (dr.Read())
            {
                entYT.YouTubeId = Convert.ToInt32(dr["YouTubeId"].ToString());
                entYT.Name = dr["Name"].ToString();
                entYT.Images = dr["Images"].ToString();
                entYT.Icon = dr["Icon"].ToString();
                entYT.Link = dr["Link"].ToString();
                entYT.ThuTu = Convert.ToInt32(dr["ThuTu"].ToString());
                entYT.TrangThai = Convert.ToByte(dr["TrangThai"].ToString());
                entYT.NgayTao = dr["NgayTao"].ToString();

            }
            dr.Close();
            sqlConnection.Close();
            return entYT;
        }
        #endregion

        #region === xoa YouTube===
        public void delete(int id)
        {
            SqlConnection sqlConnection = new SqlConnection(constr);
            sqlConnection.Open();
            cmd = new SqlCommand("sp_YouTubeDeleteById", sqlConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter[] sqla = new SqlParameter[1];
            sqla[0] = new SqlParameter("Id", DbType.Int32);
            sqla[0].Value = id;

            cmd.Parameters.AddRange(sqla);
            cmd.ExecuteNonQuery();
            sqlConnection.Close();
        }
        #endregion
    }
}
